package com.handheld.uhfdemo1;

import android.content.Context;
import android.content.Intent;
import android.net.Uri;
import android.os.Environment;
import android.text.TextUtils;

import com.blankj.utilcode.util.FileUtils;
import com.blankj.utilcode.util.LogUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;


/**
 * 表格操作类
 */
public final class ExcelHelper {

    private Context mAppContext;

    private static final Object mLock = new Object();

    private static ExcelHelper mInstance;

    private ExcelHelper(Context context) {
        mAppContext = context.getApplicationContext();
    }

    public static ExcelHelper getInstance(Context context) {
        synchronized (mLock) {
            if (mInstance == null) {
                mInstance = new ExcelHelper(context.getApplicationContext());
            }
            return mInstance;
        }
    }


    private int currentPos = 0;

    public List<ExcelDataBean> mExcelData = new ArrayList<>();

    public List<ExcelDataBean> readExcelData() {
        String filePath2 = Environment.getExternalStorageDirectory() + "/souceData" + ".xls";
        File file = new File(filePath2);
        try {
            InputStream is = new FileInputStream(file);
            Workbook book = Workbook.getWorkbook(is);
            book.getNumberOfSheets();
            Sheet sheet = book.getSheet(0);
            int Rows = sheet.getRows();

            for (int i = 1; i < Rows; ++i) {
                currentPos = i;
                String EPC = (sheet.getCell(0, i)).getContents();
                String locationCode = (sheet.getCell(1, i)).getContents();
                String installLocation = (sheet.getCell(2, i)).getContents();
                String IpAddress = (sheet.getCell(3, i)).getContents();
                String GBID = (sheet.getCell(4, i)).getContents();
                String cameraName = (sheet.getCell(5, i)).getContents();
                String RodNumber = (sheet.getCell(6, i)).getContents();
                ExcelDataBean excelDataBean = new ExcelDataBean();

                if (TextUtils.equals("0", EPC)) {
                    excelDataBean.setEpc((sheet.getCell(0, i - 1)).getContents());
                } else {
                    excelDataBean.setEpc(EPC);
                }

                if (TextUtils.isEmpty(RodNumber)) {
                    excelDataBean.setRodNumber((sheet.getCell(6, i - 1)).getContents());

                } else {
                    excelDataBean.setRodNumber(RodNumber);
                }
//                excelDataBean.setEpc(EPC);
                excelDataBean.setLocationCode(locationCode);
                excelDataBean.setInstallLocation(installLocation);
                excelDataBean.setIpAddress(IpAddress);
                excelDataBean.setGBID(GBID);
                excelDataBean.setCameraName(cameraName);
                mExcelData.add(excelDataBean);

            }
            book.close();
        } catch (Exception e) {

            LogUtils.e("yy", "e" + e);
        }
        return mExcelData;
    }

    public List<ExcelDataBean> mLocalExcelData = new ArrayList<>();

    public List<ExcelDataBean> readExcelDataLocal() {
//        List<File> files = FileUtils.listFilesInDir(Environment.getExternalStorageDirectory() + "/aaa");
        File file = new File(Environment.getExternalStorageDirectory() + "/jh/巡检表" + ".xls");
        if (FileUtils.isFileExists(file)) {
            LogUtils.e("文件存在的---------------");
            try {
                InputStream is = new FileInputStream(file);
                Workbook book = Workbook.getWorkbook(is);
                book.getNumberOfSheets();
                Sheet sheet = book.getSheet(0);
                int Rows = sheet.getRows();

                for (int i = 1; i < Rows; ++i) {
                    currentPos = i;
                    String EPC = (sheet.getCell(0, i)).getContents();
                    String locationCode = (sheet.getCell(1, i)).getContents();
                    String installLocation = (sheet.getCell(2, i)).getContents();
                    String IpAddress = (sheet.getCell(3, i)).getContents();
                    String GBID = (sheet.getCell(4, i)).getContents();
                    String cameraName = (sheet.getCell(5, i)).getContents();
                    String RodNumber = (sheet.getCell(6, i)).getContents();
                    String longtitude = (sheet.getCell(7, i)).getContents();
                    String latitude = (sheet.getCell(8, i)).getContents();
                    String Inspection = (sheet.getCell(9, i)).getContents();
                    String error = (sheet.getCell(10, i)).getContents();
                    String errorReason = (sheet.getCell(11, i)).getContents();
                    String errorFixState = (sheet.getCell(12, i)).getContents();
                    String InspectionTime = (sheet.getCell(13, i)).getContents();
                    String Memo = (sheet.getCell(14, i)).getContents();
                    ExcelDataBean excelDataBean = new ExcelDataBean();

                    if (TextUtils.equals("0", EPC)) {
                        excelDataBean.setEpc((sheet.getCell(0, i - 1)).getContents());
                    } else {
                        excelDataBean.setEpc(EPC);
                    }

                    if (TextUtils.isEmpty(RodNumber)) {
                        if(i>1){
                            excelDataBean.setRodNumber((sheet.getCell(6, i - 1)).getContents());
                        }else{
                            excelDataBean.setRodNumber("");
                        }

                    } else {
                        excelDataBean.setRodNumber(RodNumber);
                    }
                    excelDataBean.setLocationCode(locationCode);
                    excelDataBean.setInstallLocation(installLocation);
                    excelDataBean.setIpAddress(IpAddress);
                    excelDataBean.setGBID(GBID);
                    excelDataBean.setCameraName(cameraName);
                    excelDataBean.setLongitude(longtitude);
                    excelDataBean.setLatitude(latitude);
                    excelDataBean.setInspection(Inspection);
                    excelDataBean.setError(error);
                    excelDataBean.setErrorReason(errorReason);
                    excelDataBean.setErrorFixState(errorFixState);
                    excelDataBean.setInspectionTime(InspectionTime);
                    excelDataBean.setMemo(Memo);
                    mLocalExcelData.add(excelDataBean);

                }
                book.close();
            } catch (Exception e) {

                LogUtils.e("yy", "e" + e);
            }
            return mLocalExcelData;
        } else {
            return null;
        }


    }

    /*导出excel*/
    public void exportExcel(Context context, List<ExcelDataBean> exportData) {

        File filePath = new File(Environment.getExternalStorageDirectory() + "/jh/巡检表" + ".xls");

        if (FileUtils.isFileExists(filePath)) {
            LogUtils.e("filePathdelete ");
            if (FileUtils.delete(filePath)) {
                createFile(context, exportData);
            }
        } else {
            String[] title = {"EPC", "监控点编码", "安装地址", "IP地址", "国标ID", "一机一档摄像机名称"
                    , "杆体编号", "经度", "纬度", "巡检内容", "是否故障", "故障原因", "故障修复情况", "巡查时间", "备注"};
            File file = new File(ExcelUtil.getSDPath() + "/jh");
            makeDir(file);
            ExcelUtils.initExcel(file.toString() + "/巡检表.xls", title);
            String fileName = ExcelUtil.getSDPath() + "/jh/巡检表.xls";
            ExcelUtils.writeObjListToExcel(getRecordData(exportData), fileName, mAppContext);

            Uri uri = Uri.fromFile(filePath);
            Intent intent = new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri);
            context.sendBroadcast(intent);
        }


    }

    void createFile(Context context, List<ExcelDataBean> exportData) {
        String[] title = {"EPC", "监控点编码", "安装地址", "IP地址", "国标ID", "一机一档摄像机名称"
                , "杆体编号", "经度", "纬度", "巡检内容", "是否故障", "故障原因", "故障修复情况", "巡查时间", "备注"};
        File file = new File(ExcelUtil.getSDPath() + "/jh");
        makeDir(file);
        ExcelUtils.initExcel(file.toString() + "/巡检表.xls", title);
        String fileName = ExcelUtil.getSDPath() + "/jh/巡检表.xls";
        ExcelUtils.writeObjListToExcel(getRecordData(exportData), fileName, mAppContext);

        Uri uri = Uri.fromFile(file);
        Intent intent = new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri);
        context.sendBroadcast(intent);
    }

    private ArrayList<ArrayList<String>> recordList;

    /**
     * 将数据集合 转化成ArrayList<ArrayList<String>>
     *
     * @return
     */
    private ArrayList<ArrayList<String>> getRecordData(List<ExcelDataBean> excelData) {
        recordList = new ArrayList<>();
        for (int i = 0; i < excelData.size(); i++) {
            ExcelDataBean excelDataBean = excelData.get(i);
            ArrayList<String> beanList = new ArrayList<String>();
            beanList.add(excelDataBean.getEpc());
            beanList.add(excelDataBean.getLocationCode());
            beanList.add(excelDataBean.getInstallLocation());
            beanList.add(excelDataBean.getIpAddress());
            beanList.add(excelDataBean.getGBID());
            beanList.add(excelDataBean.getCameraName());
            beanList.add(excelDataBean.getRodNumber());
            beanList.add(excelDataBean.getLatitude());
            beanList.add(excelDataBean.getLongitude());
            beanList.add(excelDataBean.getInspection());
            beanList.add(excelDataBean.getError());
            beanList.add(excelDataBean.getErrorReason());
            beanList.add(excelDataBean.getErrorFixState());
            beanList.add(excelDataBean.getInspectionTime());
            beanList.add(excelDataBean.getMemo());
            recordList.add(beanList);
        }
        return recordList;
    }


    public void makeDir(File dir) {
        if (!dir.getParentFile().exists()) {
            makeDir(dir.getParentFile());
        }
        dir.mkdir();
    }


}
